﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DA
{
    class SqlDB
    {
        public static string sConn = System.Configuration.ConfigurationManager.ConnectionStrings["TeaManageConnectionString"].ConnectionString;
        public static SqlConnection con = new SqlConnection(sConn);
        /// <summary>
        /// 返回SqlCommand
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlCommand sqlCom(string sql)
        {
            SqlCommand com = new SqlCommand(sql, con);
            return com;
        }

        /// <summary>
        /// 返回SqlDataAdapter
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataAdapter sqlDa(string sql)
        {
            SqlDataAdapter da = new SqlDataAdapter(sqlCom(sql));
            return da;
        }
        /// <summary>
        /// 增删改方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool ExcuteSql(string sql)
        {
            SqlConnection conn = new SqlConnection(sConn);
            SqlCommand com = new SqlCommand();
            com.Connection = conn;
            com.CommandText = sql;

            int result = -1;

            conn.Open();
            try
            {
                result = com.ExecuteNonQuery();
                return true;

            }
            catch
            {

                return false;

            }
            finally
            {
                conn.Close();
            }





        }
        
        /// <summary>
        /// 查询方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet Query(string sql)
        {
            SqlDataAdapter sda = new SqlDataAdapter(sql, sConn);
            DataSet ds = new DataSet();
            sda.Fill(ds);

            return ds;
        }


        //处理存储过程
        public static bool ExcutePro(string proName, SqlParameter[] ss)
        {
            SqlConnection con = new SqlConnection(sConn);
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = proName;  //pro_insertOne

            //处理参数
            foreach (SqlParameter s in ss)
            {
                com.Parameters.Add(s);
            }
            int isSuccess = -1;
            con.Open();
            try
            {
                isSuccess = com.ExecuteNonQuery();
                if (isSuccess > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch
            {
                return false;
            }
            finally
            {
                con.Close();
            }


        }

        //方法的重载
        public static string ExcutePro(string proName, SqlParameter ss)
        {
            SqlConnection con = new SqlConnection(sConn);
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = proName;  //pro_insertOne

            //处理参数

            com.Parameters.Add(ss);
            string newId;

            con.Open();
            try
            {
                com.ExecuteNonQuery();
                newId = com.Parameters[0].Value.ToString();

            }
            catch
            {
                newId = null;
            }
            finally
            {

                con.Close();
            }

            return newId;

        }
    }
}
